/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookstore.dao;

import bookstore.idao.IBookDao;
import bookstore.iservice.ICategoryService;
import bookstore.model.Book;
import bookstore.model.Category;
import bookstore.model.Customer;
import bookstore.service.CategoryService;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class BsBookDao implements IBookDao {
     @Override
     public List<Book> findAll()
     {
         return null;
     }

    @Override
    public List<Book> findByPage(Integer catId, String bookName, Integer pageSize, Integer pageNo) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<Book>();
        try {
            con = MySQLHelper.connect();
            String sql = "select a.cat_name,b.* from bs_category a,bs_book b where b.bookName like ? limit " + (pageNo - 1) * pageSize + "," + pageSize;
            if (catId != null) {
                sql = "select a.cat_name,b.* from bs_category a,bs_book b where b.cat_id=" + catId + " and b.bookName like ? limit " + (pageNo - 1) * pageSize + "," + pageSize;
            }
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + bookName + "%");
            rs = ps.executeQuery();
            Book book = null;
            Category category = null;
            while (rs.next()) {
                category.setName(rs.getString("cat_name"));
                book.setId(rs.getInt("book_id"));
                category.setId(rs.getInt("cat_id"));
                book.setCategory(category);
                book.setName(rs.getString("book_name"));
                book.setPublisher(rs.getString("book_publisher"));
                book.setIsbn(rs.getString("book_isbn"));
                book.setPicture(rs.getString("book_picture"));
                book.setPrice(rs.getFloat("book_price"));
                book.setAuthor(rs.getString("book_author"));
                book.setDesc(rs.getString("book_desc"));
                book.setCount(rs.getInt("book_count"));
                book.setDiscount(rs.getInt("book_discount"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return list;
    }

    @Override
    public List<Book> findByCatId(Integer catId, Integer pageNo, Integer pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<Book>();
        try {

            con = MySQLHelper.connect();
            String sql = "select a.cat_name,b.* from bs_category a,bs_book b where b.cat_id=" + catId + " and a.cat_id=" + catId + " limit  " + (pageNo - 1) * pageSize + "," + pageSize + "";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book book = new Book();
                Category category = new Category();
                category.setName(rs.getString("cat_name"));
                book.setId(rs.getInt("book_id"));
                category.setId(rs.getInt("cat_id"));
                book.setCategory(category);
                book.setName(rs.getString("book_name"));
                book.setPublisher(rs.getString("book_publisher"));
                book.setIsbn(rs.getString("book_isbn"));
                book.setPicture(rs.getString("book_picture"));
                book.setPrice(rs.getFloat("book_price"));
                book.setAuthor(rs.getString("book_author"));
                book.setDesc(rs.getString("book_desc"));
                book.setCount(rs.getInt("book_count"));
                book.setDiscount(rs.getInt("book_discount"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLHelper.closeResult(rs);
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return list;
    }

    @Override
    public void updatePassword(Book admin) {

    }

    @Override
    public void updateMessage(Book admin) {

    }
    
     @Override
    public void updateBook(Book book)
    {
         Connection con = null;
        PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "update bs_book set cat_id=?,book_name=?,book_publisher=?,book_isbn=?,book_picture=?,book_price=?,book_author=?,book_desc=?,book_count=?,book_discount=? where book_id=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1,book.getCategory().getId());
            ps.setString(2, book.getName());
            ps.setString(3,  book.getPublisher());
            ps.setString(4,  book.getIsbn());
            ps.setString(5,  book.getPicture());
            ps.setFloat(6,  book.getPrice());
            ps.setString(7,  book.getAuthor());
            ps.setString(8,  book.getDesc());
            ps.setInt(9,  book.getCount());
            ps.setInt(10,  book.getDiscount());
            ps.setInt(11, book.getId());
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }
    @Override
    public Integer findCount(Integer categoryid, String name) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void insert(Book book) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "insert into bs_book(cat_id,book_name,book_publisher,book_isbn,book_picture,book_price,book_author,book_desc,book_count,book_discount) values(?,?,?,?,?,?,?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setInt(1, book.getCategory().getId());
            ps.setString(2, book.getName());
            ps.setString(3, book.getPublisher());
            ps.setString(4, book.getIsbn());
            ps.setString(5, book.getPicture());
            ps.setFloat(6, book.getPrice());
            ps.setString(7, book.getAuthor());
            ps.setString(8, book.getDesc());
            ps.setInt(9, book.getCount());
            ps.setInt(10, book.getDiscount());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }

    @Override
    public void update(Book obj) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void delete(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = MySQLHelper.connect();
            String sql = "delete from bs_book where book_id=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
    }

    @Override
    public Book findById(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ICategoryService categoryService = new CategoryService();
        categoryService.setCategoryDao(new CategoryDao());
        Book book =null;
        try
        {
             con = MySQLHelper.connect();
             String sql =" select a.*,b.cat_name from bs_category b,bs_book a where a.book_id ="+id+" and a.cat_id = b.cat_id";
              ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
             book = new Book();
                Category category = new Category();
                category.setName(rs.getString("cat_name"));
                book.setId(rs.getInt("book_id"));
                category.setId(rs.getInt("cat_id"));
                book.setCategory(category);
                book.setName(rs.getString("book_name"));
                book.setPublisher(rs.getString("book_publisher"));
                book.setIsbn(rs.getString("book_isbn"));
                book.setPicture(rs.getString("book_picture"));
                book.setPrice(rs.getFloat("book_price"));
                book.setAuthor(rs.getString("book_author"));
                book.setDesc(rs.getString("book_desc"));
                book.setCount(rs.getInt("book_count"));
                book.setDiscount(rs.getInt("book_discount"));
                
        }
        catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
            
        }
       return book;
    }

    @Override
    public List<Book> findAll(Integer pageNo, Integer pageSize) {
         Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<Book>();
        ICategoryService categoryService = new CategoryService();
        categoryService.setCategoryDao(new CategoryDao());
        try {
            con = MySQLHelper.connect();
            String sql = "select a.*,b.cat_name from bs_book a,bs_category b where a.cat_id=b.cat_id limit " + (pageNo - 1) * pageSize + "," + pageSize; 
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            Book book = null;
            Category category = null;
            while (rs.next()) {
                book = new Book();
                category = new Category();
                category = categoryService.findCategoryById(rs.getInt("cat_id"));
                book.setId(rs.getInt("book_id"));
                book.setCategory(category);
                book.setName(rs.getString("book_name"));
                book.setPublisher(rs.getString("book_publisher"));
                book.setIsbn(rs.getString("book_isbn"));
                book.setPicture(rs.getString("book_picture"));
                book.setPrice(rs.getFloat("book_price"));
                book.setAuthor(rs.getString("book_author"));
                book.setDesc(rs.getString("book_desc"));
                book.setCount(rs.getInt("book_count"));
                book.setDiscount(rs.getInt("book_discount"));
                list.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLHelper.closePreparedStatement(ps);
            MySQLHelper.closeConneciton(con);
        }
        return list;
    }

    @Override
    public int findAllCount() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
